#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：123.py 
@File ：测试数据准备构建.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2025/1/3 上午11:05 
"""

import pyodbc
import pandas as pd
import os
import shutil
from datetime import datetime

from sqlalchemy import create_engine

# SQL Server 配置
SQLSERVER_SERVER = '127.0.0.1'
SQLSERVER_PORT = '1433'
SQLSERVER_DATABASE = 'ColdRollingFormal'
SQLSERVER_USER = 'sa'
SQLSERVER_PASSWORD = '123456'
SQLSERVER_CHARSET = 'UTF-8'


# 连接数据库获取数据
def fetch_data_from_sqlserver(server, database, table_name, username, password):
	conn_str = f'mssql+pyodbc://{username}:{password}@{server},{SQLSERVER_PORT}/{database}?driver=ODBC+Driver+17+for+SQL+Server&trusted_connection=yes'
	engine = create_engine(conn_str)
	query = f"SELECT roll_number, implementation_rate FROM {table_name}"
	df = pd.read_sql(query, engine)
	return df


# 获取某个字段的数据并转化为列表
def get_column_data(df, column_name):
	return df[column_name].tolist()


# 遍历列表，匹配文件夹并补全路径
def get_matching_folders(root_dir, search_list):
	folder_matches = []

	for item in search_list:
		match_folders = []
		for root, dirs, files in os.walk(root_dir):
			for dir_name in dirs:
				if str(item) in dir_name:
					match_folders.append(os.path.join(root, dir_name))

		folder_matches.append(match_folders)

	return folder_matches


# 将两个文件夹复制到目标文件夹中
def copy_folders_to_target(folders, target_dir):
	for folder_list in folders:
		for folder in folder_list:
			if folder:
				# 获取对应的全数字文件夹
				digit_folder = [element for element in folder.split("_") if element.isdigit() and len(element) == 14]
				if digit_folder:
					digit_folder = digit_folder[0]
					digit_folder_path = os.path.join(root_dir, digit_folder)
				else:
					continue

				target_folder = os.path.join(target_dir, os.path.basename(folder))
				target_digit_folder_path = os.path.join(target_folder, digit_folder)
				if os.path.exists(target_folder):
					shutil.rmtree(target_folder)  # 删除已存在的文件夹

				if os.path.exists(folder) and os.path.exists(digit_folder_path):
					shutil.copytree(folder, target_folder)
					shutil.copytree(digit_folder_path, target_digit_folder_path)


# 将全为数字的文件夹移动到对应存在非数字的文件夹中
def move_folders_to_non_numeric(folders):
	for folder in folders:
		numeric_folders = [f for f in folder if f.isdigit()]
		non_numeric_folders = [f for f in folder if not f.isdigit()]

		if numeric_folders and non_numeric_folders:
			for numeric_folder in numeric_folders:
				shutil.move(numeric_folder, non_numeric_folders[0])


# 将全为数字的文件夹名称从字符串解析为年月日时分秒格式
def parse_folder_name_to_datetime(folder_name):
	if folder_name.isdigit():
		timestamp = datetime.strptime(folder_name, '%Y%m%d%H%M%S')
		return timestamp.strftime('%Y年%m月%d日')
	return folder_name


# 将存在非数字的文件夹重命名
def rename_folder_with_date_and_review(folder_path, review_field_value):
	folder_name = os.path.basename(folder_path)
	date_prefix = [element for element in folder_name.split('_') if element.isdigit() and len(element) == 14][0]
	date_str = parse_folder_name_to_datetime(date_prefix)

	new_folder_name = f"{date_str}_{folder_name}_{review_field_value}"

	folder_path = os.path.join(target_dir, folder_name)
	new_folder_path = os.path.join(target_dir, new_folder_name)

	if os.path.exists(folder_path):
		os.rename(folder_path, new_folder_path)

	return new_folder_path


# 主函数，整合步骤
def process_files_and_folders(server, database, table_name, username, password, root_dir, target_dir):
	# 1. 从数据库获取数据
	df = fetch_data_from_sqlserver(server, database, table_name, username, password)

	# 筛选，只保留roll_number为14位的数据
	df = df[df['roll_number'].str.len() == 14]

	# 2. 获取目标列的数据
	roll_number_data = get_column_data(df, 'roll_number')  # 获取需要匹配的文件夹字段
	implementation_rate_data = get_column_data(df, 'implementation_rate')  # 获取评价字段

	# 3. 获取匹配的文件夹
	folder_matches = get_matching_folders(root_dir, roll_number_data)

	# 4. 复制文件夹到目标目录
	copy_folders_to_target(folder_matches, target_dir)

	# # 5. 移动文件夹到非数字文件夹中
	# move_folders_to_non_numeric(roll_number_data)

	# 6. 重命名文件夹并添加评价字段
	for i, folder_list in enumerate(folder_matches):
		for folder in folder_list:
			if folder:
				new_folder_path = rename_folder_with_date_and_review(folder, implementation_rate_data[i])
				print(f"Folder {folder} renamed to {new_folder_path}")


# 示例调用（替换为实际值）
if __name__ == "__main__":
	# 根目录和目标文件夹路径
	root_dir = './root_dir'  # 根据实际路径替换
	target_dir = './target_dir'  # 根据实际路径替换

	# 调用主处理函数
	process_files_and_folders(SQLSERVER_SERVER, SQLSERVER_DATABASE, 'plan_rate_evaluate', SQLSERVER_USER,
							  SQLSERVER_PASSWORD, root_dir, target_dir)
